Meet NULL the UNKNOWN

Mixit Lyon
2022-05-25

Who am I

• Lætitia Avrot
• PostgreSQL Europe board member
• #PostgresWomen co-founder
• EDB Field CTO
• @l_avrot
• mydbanotebook.org

Meet NULL the UNKNOWN

• What's NULL?
• What's UNKNOWN?
• What's a boolean?
• What's the 3-valued logic ?
• Why should I care?

Definitions

NULL

Special value that is used to indicate the absence of any data value

Every data type includes a special value, called the null value, sometimes denoted by the keyword NULL.

• an empty string
• a string with only spaces in it
• the string 'NULL'
• 0

UNKNOWN

Value of the Boolean data type is either true or false.
The truth value of unknown is sometimes represented by the null value.

• true
• false
• unknown
• null

test=> create table test (test boolean);
CREATE TABLE
test=> insert into test values (true),(false),(unknown),(null)
;

2019-10-14 18:08:52.088 CEST [5688] ERROR:  column "unknown"
 does not exist at character 41

2019-10-14 18:08:52.088 CEST [5688] STATEMENT:  insert into
 test values (true),(false),(unknown),(null);
ERROR:  column "unknown" does not exist
LINE 1: insert into test values (true),(false),(unknown),
(null);

test=> insert into test values (true),(false),(null),
(null);
INSERT 0 4

test=> select * from test;
 test 
------
 t
 f
 
 
(4 rows)

<boolean literal> ::=
    TRUE
  | FALSE
  | UNKNOWN

This specification does not make a distinction between the null value of the boolean data type and the truth value Unknown that is the result of an SQL predicate, search condition, or boolean value expression; they may be used interchangeably to mean exactly the same thing.

Displaying null

          test=# \pset null 'Ada Lovelace'
Null display is "Ada Lovelace".

test=# select * from test;
     test     
--------------
 t
 f
 Ada Lovelace
 Ada Lovelace
(4 rows)


test=# 

3-value logic

          select coalesce(ros.a::text,'unknown') as and_truth_table,
ros.a and cols.a as t,
ros.a and cols.b as f,
ros.a and cols.c as unknown
from (values (true, false, null::boolean)) as cols (a,b,c),
  (values (true),(false),(null)) as ros (a)

3-value logic

           and_truth_table |      t       | f |   unknown    
-----------------+--------------+---+--------------
 true            | t            | f | Ada Lovelace
 false           | f            | f | f
 unknown         | Ada Lovelace | f | Ada Lovelace
(3 rows)

3-value logic

It's time for a quiz!

How many rows will this query return ?

select a, b
from (values (1, true),
             (2, false),
             (3, null)) as t (a,b)
where null;

How many rows will this query return ?

select a, b
from (values (1, true), (2, false), (3, null)) as t (a,b)
where null;

• 0
• 1
• 3
• Aminata Sana Congo

How many rows will this query return ?

select a, b
from (values (1, true),
             (2, false),
             (3, null)) as t (a,b)
where null;

 a | b  
---+----
(0 rows)

null in a where clause is treated like false.

How many rows will this query return ?

select a, b 
from (values (1, 'aa'),
             (2, 'bb'),
             (3, null)) as t (a,b)
where t.b <> 'aa';

How many rows will this query return ?

select a, b 
from (values (1, 'aa'), (2, 'bb'), (3, null)) as t (a,b)
where t.b <> 'aa';

• 0
• 1
• 2
• Maria Mitchel

How many rows will this query return ?

select a, b 
from (values (1, 'aa'),
             (2, 'bb'),
             (3, null)) as t (a,b)
where t.b <> 'aa';

 a | b  
---+----
 2 | bb
(1 row)

Because inequality or equality operations are null when one of the operand is null and null in a where clause is treated like false!

How many rows will this query return ?

select a, b 
from (values (1, 'aa'),
             (2, 'bb'),
             (3, null)) as t (a,b)
where t.b is distinct from 'aa';

How many rows will this query return ?

select a, b 
from (values (1, 'aa'), (2, 'bb'), (3, null)) as t (a,b)
where t.b is distinct from 'aa';

• 0
• 1
• 2
• Nicole-Reine Étable de la Brière Lepaute

How many rows will this query return ?

select a, b 
from (values (1, 'aa'),
             (2, 'bb'),
             (3, null)) as t (a,b)
where t.b is distinct from 'aa';

 a | b  
---+----
 2 | bb
 3 | 
(2 rows)

is distinct means "are not identical".
For SQL standard, identical for null value is :

If V1 and V2 are both the null value, then V1 is identical to V2.

How many rows will this query return ?

select a, b
from (values (1, 'aa'),
             (2, 'bb'),
             (3, null)) as t (a,b)
where t.b in ('aa',null);

How many rows will this query return ?

select a, b
from (values (1, 'aa'), (2, 'bb'), (3, null)) as t (a,b)
where t.b in ('aa',null);

• 0
• 1
• 2
• Annie Jump Cannon

How many rows will this query return ?

select a, b
from (values (1, 'aa'),
             (2, 'bb'),
             (3, null)) as t (a,b)
where t.b in ('aa',null);

 a | b 
---+---
 1 | aa
(1 row)

t.b in ('aa',null) is equivalent to t.b = 'aa' or t.b = null

As null = null is null and it's in the where clause, it's considered false

How many rows will this query return ?

select a, b
from (values (1, 'aa'),
             (2, 'bb'),
             (3, null)) as t (a,b)
where t.b not in ('aa',null);

How many rows will this query return ?

select a, b
from (values (1, 'aa'), (2, 'bb'), (3, null)) as t (a,b)
where t.b not in ('aa',null);

• 0
• 1
• 2
• Alice Lee

How many rows will this query return ?

select a, b
from (values (1, 'aa'),
             (2, 'bb'),
             (3, null)) AS t (a,b)
where t.b not in ('aa',null);

 a | b 
---+---
(0 rows)
          

t.b not in ('aa',null) is equivalent to not t.b in ('aa',null). So it's equivalent to 'aa' <> 'aa' and null<> null.

Null with and is always null and null in the where clause is false.

How many rows will this query return ?

select a, b
from (values (1),
             (2),
             (null)) as t (a,b)
where t.a between 1 and null;

How many rows will this query return ?

select a, b
from (values (1), (2), (null)) as t (a,b)
where t.a between 1 and null;

• 0
• 1
• 2
• Jaime Levy

How many rows will this query return ?

select a, b
from (values (1),
             (2),
             (null)) as t (a,b)
where t.a between 1 and null;

 a | b 
---+---
(0 rows)
          

“X BETWEEN ASYMMETRIC Y AND Z” is equivalent to “X>=Y AND X<=Z”

How many rows will this query return?

select a, b
from (values (1, true),
             (2, false),
             (3, null)) as t (a,b)
where t.b < true;

How many rows will this query return?

select a, b
from (values (1, true), (2, false), (3, null)) as t (a,b)
where t.b < true;

• 0
• 1
• 2
• Commodore Grace M. Hopper

How many rows will this query return?

select a, b
from (values (1, true),
             (2, false),
             (3, null)) as t (a,b)
where t.b < true;

 a | b 
---+---
 2 | f
(1 row)

The value True is greater than the value False, and any comparison involving the null value or an Unknown truth value will return an Unknown result.

What will this query return ?

          select (null=1)
       or (1=1) as "Annie Easley";

What will this query return ?

          select (null=1) or (1=1) as "Annie Easley";

• true
• false
• null
• Annie Easley

What will this query return ?

          select (null=1)
       or (1=1) as "Annie Easley";

 Annie Easley 
--------------
t
(1 row)

null or true is true

What will this query return ?

          select null is null is null
       is null is null
       as "Margaret Hamilton";

What will this query return ?

          select null is null is null is null is null as "Margaret
Hamilton";

• true
• false
• null
• Margaret Hamilton

What will this query return ?

          select null is null is null
       is null is null
       as "Margaret Hamilton";

 Margaret Hamilton 
-------------------
 f
(1 row)
          

The first one is true, all the others are false

What will this query return ?

          select row(null) is null;

What will this query return ?

          select row(null) is null;

• true
• false
• null
• Radia Perlman

What will this query return ?

          select row(null) is null;

  ?column? 
----------
 t
(1 row)
          

• Let R be the row value predicand and let V be the value of R.
• If the value of every field of V is the null value, then [the value of “R IS NULL” is] True.

What will this query return ?

          select row(row(null)) is null;

What will this query return ?

          select row(row(null)) is null;

• true
• false
• null
• Brenda Laurel

What will this query return ?

          select row(row(null)) is null;

  ?column? 
----------
 f
(1 row)

The value of the first field is not the null value but row(null), so it's false!

What will this query return ?

select nullif(null,1);

What will this query return ?

select nullif(null,1);

• 0
• 1
• null
• Ada Lovelace

What will this query return ?

select nullif(null,1);

    nullif    
--------------
 Ada Lovelace
(1 row)
          

Nullif returns true if both values are equal and returns the first value if not.

What will this query return ?

select 'Mary Lou ' || a || 'Jepsen'
from (values (null)) as t (a);

What will this query return ?

select 'Mary Lou ' || a || 'Jepsen'
from (values (null)) as t (a);

• 0
• unknown
• Mary Lou Jepsen
• Ada Lovelace

What will this query return ?

select 'Mary Lou ' || a || 'Jepsen'
from (values (null)) as t (a);

  ?column?   
--------------
 Ada Lovelace
(1 row)

Concatenation with null is always null

Should we care?

• Yes
• No
• null